﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace InstrumentCheck.SQLHelper
{
    public class SQLHelper
    {
        //Create Parameters without size
        public static SqlParameter CreateParameter(String p_paraName, SqlDbType p_paraType, Object p_paraValue, ParameterDirection p_paraDirection)
        {
            SqlParameter para = new SqlParameter(p_paraName, p_paraType);
            para.Value = p_paraValue;
            para.Direction = p_paraDirection;
            return para;
        }

        //Create Parameters with size
        public static SqlParameter CreateParameter(String p_paraName, SqlDbType p_paraType, Object p_paraValue, int p_size, ParameterDirection p_paraDirection)
        {
            SqlParameter parameter = new SqlParameter(p_paraName, p_paraType, p_size);
            parameter.Value = p_paraValue;
            parameter.Direction = p_paraDirection;
            return parameter;
        }

        //Create Parameters without value
        public static SqlParameter CreateParameter(String p_paraName, SqlDbType p_paraType, ParameterDirection p_paraDirection)
        {
            SqlParameter para = new SqlParameter(p_paraName, p_paraType);
            para.Direction = p_paraDirection;
            return para;
        }

        //Select data,Fill to DataSet
        public static void Fill(SqlDataAdapter da, DataSet ds, String p_content, CommandType p_commandType, SqlParameter[] p_parameters)
        {
            SqlConnection cn = null;
            cn = Helper.GetConnection();
            Helper.OpenConnection(cn);

            da.SelectCommand = new SqlCommand();
            da.SelectCommand.Connection = cn;
            da.SelectCommand.CommandType = p_commandType;
            da.SelectCommand.CommandText = p_content;

            if (p_parameters != null)
            {
                if (p_parameters.Length != 0)
                {
                    foreach (SqlParameter parameters in p_parameters)
                    {
                        da.SelectCommand.Parameters.Add(parameters);
                    }
                }
            }
            da.Fill(ds);

            if (cn != null)
                Helper.CloseConnection(cn);
        }

        public static void ExecuteNonQuery(CommandType p_commandType, String p_content, SqlParameter[] p_parameters)
        {
            SqlConnection cn = null;
            SqlCommand cmd = null;

            try
            {
                cn = Helper.GetConnection();
                Helper.OpenConnection(cn);

                cmd = new SqlCommand(p_content, cn);
                cmd.CommandType = p_commandType;
                if (p_parameters != null && p_parameters.Length > 0)
                {
                    foreach (SqlParameter parameter in p_parameters)
                    {
                        cmd.Parameters.Add(parameter);
                    }
                }
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cn != null)
                    Helper.CloseConnection(cn);
                if (cmd != null)
                    cmd.Dispose();
            }
        }

        public static void TransExecuteNonQuery(CommandType p_commandType, String p_content, SqlParameter[] p_parameters)
        {
            SqlConnection cn = null;
            SqlCommand cmd = null;
            SqlTransaction trans = null;
            try
            {
                cn = Helper.GetConnection();
                Helper.OpenConnection(cn);
                cmd = new SqlCommand(p_content, cn);
                trans = cn.BeginTransaction();
                cmd.Transaction = trans;
                cmd.CommandType = p_commandType;
                if (p_parameters != null && p_parameters.Length > 0)
                {
                    foreach (SqlParameter parameter in p_parameters)
                    {
                        cmd.Parameters.Add(parameter);
                    }
                }
                cmd.ExecuteNonQuery();
                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                throw ex;
            }
            finally
            {
                if (cn != null)
                    Helper.CloseConnection(cn);
                if (cmd != null)
                    cmd.Dispose();
            }
        }
    }
}
